package com.bo.Dao.Impl;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.bo.Dao.ProductDao;
import com.bo.Entity.MyImage;
import com.bo.Entity.MyPage;
import com.bo.Entity.Product;
import com.bo.util.MyLobStreamingResultSetExtractor;


@Repository
public class ProductDaoImpl implements ProductDao{
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private LobHandler lobHandler;
	
	

	public List<Product> createPage(final MyPage<Product> myPage){

		return jdbcTemplate.query("select id,name,image_name1, model,time ,company_id from product_int where company_id=? order by time desc"
				            ,new Object[]{myPage.getCompanyId()},new ResultSetExtractor<List<Product>>(){

			public List<Product> extractData(ResultSet rs) throws SQLException,DataAccessException {
				
				List<Product> result=new ArrayList<Product>();
				int count=0;
				while(rs.next()&&count<myPage.getFirstRow()+myPage.getCountPerPage()){
					
					if(count>=myPage.getFirstRow()){
						
						  Product product=new Product();
							product.setId(rs.getInt("id"));
							product.setTitle(rs.getString("name"));
							product.setModel(rs.getInt("model"));
							product.setCompanyId(rs.getInt("company_id"));
							product.setTime(rs.getString("time"));

							product.setImage1(new MyImage(1,rs.getString("image_name1")));

						  result.add(product);
					}
					
					count++;
				}
				
			  return result;
			}
		});
	}

	

	public List<Product> getAllProduct(int companyId){
		
		return this.jdbcTemplate.query(
				"select id,name,content,model,time,company_id ,image_name1,image_name2,image_name3" +
				" from product_int where company_id=? order by time desc",
				 new Object[]{companyId},new ProductRowMapper());
	}
	
	
	

	public Product getProduct(int id){
		
		String sql="select id,name,model,content,time, company_id ,image_name1,image_name2" +
				",image_name3 from product_int where id=?";
		
		return this.jdbcTemplate.queryForObject(sql,new Object[]{id}, new ProductRowMapper());
	}
	

	

	public int save(Product product){
		
	   MyImage image1=product.getImage1();
	   MyImage image2=product.getImage2();
	   MyImage image3=product.getImage3();
		
	   SqlLobValue lobValue1=new SqlLobValue(image1.getInputStream(), (int) image1.getSize(), lobHandler);
	   SqlLobValue lobValue2=new SqlLobValue(image2.getInputStream(), (int) image2.getSize(), lobHandler);
	   SqlLobValue lobValue3=new SqlLobValue(image3.getInputStream(), (int) image3.getSize(), lobHandler);
      	
		
	 return  this.jdbcTemplate.update(
	   "INSERT INTO product_int (name,content,model,time,company_id,image_name1,image1,image_name2,image2,image_name3,image3) VALUES (?,?,?,?,?,?,?,?,?,?,?)", 
	   new Object[]{product.getTitle(),product.getContent(),product.getModel(),product.getTime(),product.getCompanyId()
			      ,image1.getImageName(),lobValue1,image2.getImageName(),lobValue2,image3.getImageName(),lobValue3}
	  ,new int[]{Types.VARCHAR,Types.VARCHAR,Types.INTEGER,Types.VARCHAR,Types.INTEGER
			    ,Types.VARCHAR,Types.BLOB,Types.VARCHAR,Types.BLOB,Types.VARCHAR,Types.BLOB});
	}
	
	
	

	public int update(Product product){
		
		   MyImage image1=product.getImage1();
		   MyImage image2=product.getImage2();
		   MyImage image3=product.getImage3();
			
		   SqlLobValue lobValue1=new SqlLobValue(image1.getInputStream(), (int) image1.getSize(), lobHandler);
		   SqlLobValue lobValue2=new SqlLobValue(image2.getInputStream(), (int) image2.getSize(), lobHandler);
		   SqlLobValue lobValue3=new SqlLobValue(image3.getInputStream(), (int) image3.getSize(), lobHandler);
	      	
			
		 return  this.jdbcTemplate.update(
		   "UPDATE product_int SET name=?,content=?,model=?,image_name1=?,image1=?,image_name2=?,image2=?,image_name3=?,image3=? WHERE id=?", 
		   new Object[]{product.getTitle(),product.getContent(),product.getModel()
				      ,image1.getImageName(),lobValue1,image2.getImageName(),lobValue2,image3.getImageName(),lobValue3,product.getId()}
		  ,new int[]{Types.VARCHAR,Types.VARCHAR,Types.INTEGER
				    ,Types.VARCHAR,Types.BLOB,Types.VARCHAR,Types.BLOB,Types.VARCHAR,Types.BLOB,Types.INTEGER});
		}
	
	

	public int delete (int id){
		
		String sql="DELETE FROM product_int WHERE id=?";
		
		return jdbcTemplate.update(sql, id);
	}
	
	
	

	public int count(int companyId) {
		
		return this.jdbcTemplate.queryForInt("select count(*) from product_int where company_id="+companyId);
	}
	
	

	
	public void getImage(final int id,final MyImage image){
		
		String sql="SELECT image"+image.getId()+" FROM product_int WHERE id=?";
		
		jdbcTemplate.query(sql, new Object[] {id}
                       ,new MyLobStreamingResultSetExtractor(lobHandler, id, image));
	}
	
	
	
	
	private class ProductRowMapper implements RowMapper<Product>{

		public Product mapRow(ResultSet rs, int rowNum) throws SQLException {

			Product product=new Product();
			
			product.setId(rs.getInt("id"));
			product.setTitle(rs.getString("name"));
			product.setContent(rs.getString("content"));
			product.setTime(rs.getString("time"));
			product.setModel(rs.getInt("model"));
			product.setCompanyId(rs.getInt("company_id"));
			
			product.setImage1(new MyImage(1,rs.getString("image_name1")));
			product.setImage2(new MyImage(2,rs.getString("image_name2")));
			product.setImage3(new MyImage(3,rs.getString("image_name3")));
			
			return product;
		}
	}
}
